package com.elite;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Types;
import java.util.List;

import javax.sql.DataSource;

import oracle.jdbc.OracleTypes;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;

public class FtpBean {
	private static final Log log=LogFactory.getLog(FtpBean.class);
    private DataSource dataSource;
    private DataSourceTransactionManager tran;
    private PlatformTransactionManager transactionManager;
    
	public PlatformTransactionManager getTransactionManager() {
		return transactionManager;
	}
	public void setTransactionManager(PlatformTransactionManager transactionManager) {
		this.transactionManager = transactionManager;
	}
	public DataSource getDataSource() {
		return dataSource;
	}
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}
	public DataSourceTransactionManager getTran() {
		return tran;
	}
	public void setTran(DataSourceTransactionManager tran) {
		this.tran = tran;
	}
    public List chaxun(String sql) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List resultList = jdbcTemplate.queryForList(sql);
        return resultList;
  }
    public int update(String sql) throws Exception {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        int resultList = jdbcTemplate.update(sql);
        return resultList;
  }
    public String p_smsout(String name1,String name2,String name3,String name4) {
    	log.info("---------p_smsout------------");
    	final String a=name1;
    	final String b=name2;
    	final String c=name3;
    	final String d=name4;
    	log.info("a:"+a);
    	log.info("b:"+b);
    	log.info("c:"+c);
    	log.info("d:"+d);
    	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){
                    String storedProc = "{call p_smsout(?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs = null;
                    try{
                   	 cs = con.prepareCall(storedProc);
                   }catch(Exception e){
                   	log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                   	cs.setString(1,a);// 设置输入参数的值
                   	cs.setString(2, b);// 设置输入参数的值
                   	cs.setString(3, c);// 设置输入参数的值
                	cs.setString(4, d);// 设置输入参数的值
                   	cs.setString(5,"");// 设置输入参数的值
                   }catch(Exception e){
                   	log.info("设置输入参数的值错误:"+e);
                   }
                   try{
                   	cs.registerOutParameter(5,Types.VARCHAR);// 注册输出参数的类型
                   }catch(Exception e){
                   	log.info("注册输出参数的类型错误:"+e);
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+e);
                    }
                    try{
                    	   return cs.getString(5);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(5):"+e);
                    	return null;
                    }
            }
        }).toString();
        log.info("param2Value:"+param2Value);
        return param2Value;
    }      
    
    public String p_smsin(String name1,String name2,String name3,String name4) {
    	log.info("---------p_smsin------------");
    	final String a=name1;
    	final String b=name2;
    	final String c=name3;
    	final String d=name4;
    	log.info("a:"+a);
    	log.info("b:"+b);
    	log.info("c:"+c);
    	log.info("d:"+d);
    	JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        String param2Value=jdbcTemplate.execute(
            new CallableStatementCreator() {
                public CallableStatement createCallableStatement(Connection con){
                    String storedProc = "{call p_smsin(?,?,?,?,?)}";// 调用的sql
                    CallableStatement cs = null;
                    try{
                   	 cs = con.prepareCall(storedProc);
                   }catch(Exception e){
                   	log.info("cs = con.prepareCall(storedProc)错误:"+e);
                   }
                   try{
                   	cs.setString(1,a);// 设置输入参数的值
                   	cs.setString(2, b);// 设置输入参数的值
                   	cs.setString(3, c);// 设置输入参数的值
                	cs.setString(4, d);// 设置输入参数的值
                   	cs.setString(5,"");// 设置输入参数的值
                   }catch(Exception e){
                   	log.info("设置输入参数的值错误:"+e);
                   }
                   try{
                   	cs.registerOutParameter(5,Types.VARCHAR);// 注册输出参数的类型
                   }catch(Exception e){
                   	log.info("注册输出参数的类型错误:"+e);
                   }
                    return cs;
                }
            }, new CallableStatementCallback() {
                public Object doInCallableStatement(CallableStatement cs)  {
                    try{
                    	cs.execute();
                    }catch(Exception e){
                    	log.info("cs.execute()错误:"+e);
                    }
                    try{
                    	   return cs.getString(5);// 获取输出参数的值
                    }catch(Exception e){
                    	log.info("cs.getString(5):"+e);
                    	return null;
                    }
            }
        }).toString();
        log.info("param2Value:"+param2Value);
        return param2Value;
    }      
}
